/************************************************************************
*	Description: This program gathers several registers from DST and 	*
*		data on mortgages to a complete data set on CPR (PNR) and 		*
*		family level, "Individdata" and "Familiedata" respectively.		*
*		The data sets contains information on income, dept, and assets	*
* 		for all Danish families and individuals. Further, a series of 	*
*		'stamdata' is included.											*
*		The data sets are the foundation for most of the analysis of 	*
*		households on micro data in the Bank 							*
*																		*
*	Structure:															*
*	 1) Libraries are defined.											*
*	 2) The parameters Startaar og Slutaar are set.						*
*	 3) The register of real estate, BOL, and the register of population*
*		BEF are merged.  												*
*	 4) Computes a data set over real estate valuation from FORMEJER and*
*		FORMAND.														*
*	 5) Controls existence of data sets. If a data set does not exists,	*
*		a dummy data set is created in its place.						*
* 	 6) Adjustment of the register of income, IND , and the register of	*
*		pensions contributions, INPI.
*	 7) Register data on individual level are merged for each year.		*
*		Data on the individual's mortgage is merged from 				*
*		databib.rk_individdata&AAR.										*
*	 8) Individual data for each year is merged into one big data set	*
*		on CPR level: databib.Individdata.								*
*	 9) Individual data is summarized on family level to a temporarily	*
*		family data set.												*
*	10) Register data from the family income register, FAIK, 'stamdata'	*
*		on families, FAM, are cleaned 									*
*	11) Data on car assets (FORMBIL, family level) are cleaned			*
*	12) Data on pension assets (pensionsformue) are cleaned. 			*
*		A data set is saved for each year (databib.pension&aar.)		*
*	13) The family data set is merged with FAIK and FORMBIL.			*
*		Addtionally, information on the retirement savings and car 		*
*		car purchase are added. Variables for the main person of the 	*
*		family og partner (if one exists) are defined. 					*
*		Finally a dummy for being involved in real estate in the current*
*		year is computed based on databib.Individdata.					*
*	14) Finally, a series of variables on the families are computed,	*
*		 and the final data set on family level, databib.Individdata, 	*
*		are computed. 													*
*																		*
*	Parameters:															*
*		Startaar: 	First year of output data sets.			 			*
*		Slutaar:	Last year of output data sets.						*
*																		*
*	Input: 																*
*			raw.akm			Arbejdsklassifikationsmodulet				*
*			raw.bef			Population									*
*			raw.bol			Housing count								*
*			raw.faik		Family income								*
*			raw.fam			Family										*
*			raw.formand		Assets, cooperative housing					*
*			raw.formbil		Assets, cars								*
*			raw.formejer	Assets, owner property						*
*			raw.idap		IDA Persondata								*
*			raw.ind			Income										*
*			raw.inhp		Taxable withdrawn pensions and payouts		*
*			raw.inpi		Pension payments							*
*			raw.syhb		Use of hospital, person						*
*			raw.udda		Education									*
*			databib.famejendomshandler									*
*		The above is generated in the program: "0a_ChangeOfProperty.sas"*									
*			databib.rk_Individdata										*
*		The above is generated in the program: "0b_MortgageLoans.sas"	*
*																		*
*	Output: SAS dataset: 												*
*			"databib.Familiedata"										*
*			"databib.Individdata"										*
*																		*
*																		*
************************************************************************/

dm 'cle log';
dm 'cle out';
dm 'keydef f12 "cle log; cle out; submit"';
*%let debug=CANCEL; /*Activate this line to control the syntax with put running the program, when pressing F8*/
%let debug=;       /*Activate this line to run the program when pressing F8.*/
options compress=yes;


/************************************************************************
* 1) Libraries are defined												*
************************************************************************/

libname databib "E:/ProjektDB/706172/Workdata/706172/Datas�t";
libname temp "E:/ProjektDB/706172/Workdata/706172/Person/Marie/Husholdningsprojekt/Data/temp";
libname raw   "E:/ProjektDB/706172/DST_rawdata_706172";
libname fmt  '\\srvfsenas1\data\formater\SAS formater i Danmarks Statistik\FORMATKATALOG';
options fmtsearch=(fmt.times_personstatistik fmt.brancher fmt.uddannelser fmt.geokoder work egnefmt);

/************************************************************************
* 2) Parameters Startaar and Slutaar are set							*
************************************************************************/

%let startaar = 1996; *1998;
%let slutaar = 2017; *2017;

%let startaar_1 = &startaar - 1;
%let startaar_2 = &startaar - 2;
%let slutaar_min_1 = &slutaar - 1;
%let slutaar_1 = &slutaar + 1;

/************************************************************************
* 3) BOL and BEF are merged												*
* 
* First a data set is computed, where the information from BOL are 		*
* tied to the individuals in the sample. This is done tolater determine *
* the ownership of the property, the family is living in. Specifically, *
* we want to be able to identfy the families living in cooperative		*
* housing (andelsbolig). The asset data will in many cases be misleading*
* for these families, since the value of the property does not occur 	*
* in the tax data on assets, whereas potential dept will occur as a  	*
* liability. 										                     *
************************************************************************/

*The macro checkit1(x) controls the existence of the data sets x (from startaar to slutaar+1);
%macro checkit1(dsn);
	%do i=&startaar %to &slutaar_1;
	%let h = %eval(&i-1);
		%if %sysfunc(exist(raw.&dsn&i)) = 1 %then %do;
			data &dsn&i;
				set raw.&dsn&i;
			run;
			data _null_;
			file print;
			put "The data set &dsn&i already exists";
			run;
		%end;
		%else %do;
			data &dsn&i;
				set raw.&dsn&h;
			run;
			data _null_;
			file print;
			put "The data set &dsn&i does not exist. The data set from the latest year is inserted.";
			run;
		%end;
	%end;
%mend;

* Controls BOL;
%checkit1(bol); *Runs the macro;

/*The macro andelsmarkor generates the data sets ejerforhold&i by merging BEF and BOL*/
%macro andelsmarkor;
	%do i=&startaar %to &slutaar_1;
		*Sorts BEF by KOM and BOPIKOM, saves as ejf&i in work library;
		proc sort data=raw.bef&i (keep=pnr kom bopikom) out=work.ejf&i;
			by kom bopikom;
		run &debug;
		
		*Sorts BOL after KOM and BOPIKOM and saves as BOL&i in work library (prev. to 2010);
		%if &i < 2010 %then %do; /* "Ejerforhold" was missing in BOL before 2010, should be added now (3/3)*/
			proc sort data=bol&i (keep=kom bopikom ejerforhold) out=work.bol&i;
				by kom bopikom;
			run &debug;
		%end;
		
		*Sorts BOL after KOM and BOPIKOM and saves as BOL&i in work library ina and after 2010);
		%if &i >= 2010 %then %do;
			proc sort data=bol&i (keep=kom bopikom dst_enh_ejerforholdskode rename=(dst_enh_ejerforholdskode=ejerforhold)) out=work.bol&i;
				by kom bopikom;
			run &debug;
		%end;

		/*Delets duplicates in BOL data*/
		data work.bol_unik_&i /*(rename = (kom_num = kom)) *Change of type (if necessary);*/;
			set work.bol&i;
			by kom bopikom;
			if first.bopikom=1;
		run &debug;

		/*Generates the data set ejerforhold&i by merging BOL and ejf from the work library by KOM and BOPIKOM*/
		data work.ejerforhold&i;
			merge work.ejf&i (in=a)
				  work.bol_unik_&i;
			by kom bopikom;
			if a; *Uses only observations from ejf (BEF) ;
		run &debug;
		
		/*Sorts the new data ejerforhold&i by pnr*/
		proc sort data=work.ejerforhold&i;
			by pnr;
		run &debug;
	%end;
%mend;
%andelsmarkor; *Runs the macro;

/************************************************************************
* 4) Controls if the data sets exists. If not, a dummy data set is		*
*	 generated. Additionally, a dummy data set for RK_individdata is  	*
*	 used for years before 2009.										*
*																		*
*	 All data sets need to exist for the code to run. Some data sets 	*
*	 are computed later than others. 									*
************************************************************************/

*The macro checkit2 controls a data set exists, if not, a dummy data set
 is inserted in its place;
%macro checkit2(dsn);
	%do i=&startaar_2 %to &slutaar;
	%let h = %eval(&i-1);
	%let g = %eval(&i-2);
	%let f = %eval(&i-3);
		%if %sysfunc(exist(raw.&dsn&i)) = 1 %then %do;
			data &dsn&i;
				set raw.&dsn&i;
			run;
			data _null_;
			file print;
			put "The data set &dsn&i already exists.";
			run;
		%end;
		%else %if %sysfunc(exist(raw.&dsn&h)) = 1 %then %do;
			proc sql;
				create table &dsn&i like raw.&dsn&h;
			quit;	
			data _null_;
			file print;
			put "The data set &dsn&i does not exists. A dummy data set is used.";
			run;
		%end;
		%else %if %sysfunc(exist(raw.&dsn&g)) = 1 %then %do;
			proc sql;
				create table &dsn&i like raw.&dsn&g;
			quit;	
			data _null_;
			file print;
			put "The data set &dsn&i does not exists. A dummy data set is used.";
			run;
		%end;
		/*The following 3 if-conditions are added to accomodate the shorter
		time period for FORMAND, FORMEJER and INPI */
		%else %if &dsn=formand and &i<2004 %then %do;
			proc sql;
			create table &dsn&i like raw.&dsn.2004;
			quit;	
			data _null_;
			file print;
			put "The data set &dsn&i does not exists. A dummy data set is used.";
			run;
		%end;
		%else %if &dsn=formejer and &i<2004 %then %do;
			proc sql;
			create table &dsn&i like raw.&dsn.2004;
			quit;	
			data _null_;
			file print;
			put "The data set &dsn&i does not exists. A dummy data set is used.";
			run;
		%end;
		%else %if &dsn=inpi and &i<1995 %then %do;
			proc sql;
			create table &dsn&i like raw.&dsn.1995;
			quit;	
			data _null_;
			file print;
			put "The data set &dsn&i does not exists. A dummy data set is used.";
			run;
		%end;
		%else %do;
			proc sql;
				create table &dsn&i like raw.&dsn&f;
			quit;	
			data _null_;
			file print;
			put "The data set &dsn&i does not exists. A dummy data set is used.";
			run;
		%end;
	%end;
%mend;

* Controls the data sets AKM, IDAP, INPI, IND, SYHB, INHP, FORMEJER, and FORMAND;
%checkit2(akm);
%checkit2(idap);
%checkit2(inpi);
%checkit2(ind);
%checkit2(syhb);
%checkit2(inhp);
%checkit2(formejer);
%checkit2(formand);

/* Creates data set for RK_individ */
proc sql;
	create table rk_individdatadummy like databib.rk_Individdata2009; 
quit;

/************************************************************************
* 5) Generates a data set of real estate values from FORMEJER and 		*
*	 FORMAND. 															*
*	 Contains information on an individual's assets (property, both		*
*	 "owner" and cooperative (andel)). If an individual owns multiple 	*
* 	 properties the values are summarized. If the same property is owned*
*	 by more than one person, each person is assigned a share only.		*
************************************************************************/

*The macro formue_ejendom generates ejendomsforme&i by merging formejer and formand ny pnr;
%macro formue_ejendom;
	%do i=&startaar %to &slutaar;

	*Generates formejer_temp&i from formejer (work) for non-missing pnrs;
		data formejer_temp&i;
			set formejer&i;
			if pnr ^= "";
			length kom_char $3.;
			kom_char = kom;
			komejdnr = trim(kom_char||ejdnr);
			rename markedsvaerdi = markedsvaerdi_temp;
			run;

		proc sql;
			create table formejer&i
			as select pnr, sum(markedsvaerdi_temp) as boligvaerdi, count(komejdnr) as antalejerbol
			from formejer_temp&i
			group by pnr;
		quit;

	*Generates formand_temp&i from formand (work) for non-missig pnrs;
		data formand_temp&i;
			set formand&i;
			if pnr ^= "";
			rename markedsvaerdi = markedsvaerdi_temp;
			run;
		proc sql;
			create table formand&i
			as select pnr, sum(markedsvaerdi_temp) as andboligvaerdi, count(pnr) as andelsbolig
			from formand_temp&i
			group by pnr;
		quit;
	* Generates ejendomsformue&i by merging formejer and formand ny pnr. Missing valuation
		of housing (owner and cooperative are set to 0);
		data ejendomsformue&i;
			merge formejer&i formand&i;
			by pnr;
			if boligvaerdi = . then boligvaerdi = 0;
			if andboligvaerdi = . then andboligvaerdi = 0;
			boligvaerdi_samlet = boligvaerdi + andboligvaerdi;
		run;
	%end;
%mend;

%formue_ejendom; * Runs the macro;

/************************************************************************
* 6) The income register, IND, and pensions payments, INPI, are adjusted*
*																		*
* IND: Register of income. Does not contain the variable "udlakt" prior *
*	   to 2002 (foreign assets). udlakt=. in years prior to 2002 to		*
* 	   avoid errors.													*
* INPI: Variables for pensions payments gathered here, due to some 		*
*		variables changing over time. Done to avoid mistakes later on.	*
* IDAP: The variables ARLEDGR is adjusted, since the defination changes	*
*		from 2008 							   							*
************************************************************************/

%macro reg_rette;
	%do i = &startaar_2 %to &slutaar;
		data ind&i;
			set ind&i;
			%if &i < 2002 %then %do;
				udlakt = .;
				rentvirk = .; *Interest rate income from corporation, including
				net interest rate income from abroad including income tax liable 
				capital gain (kursgevinster) in corporation.;
			%end;
			%if &i<1997 %then %do;
			formrest_ny05=.;
			udprigael=.;
			%end;
		run;

		/*Generates pensindbetal (pensions payments) as the sum of arbpen10-arbpen18, arbpen11_2012 in the place of arbpen11 after 2010 and arbpen4445.
		All describing pension payments to employer-administered pension scheme.
		The variables pripen10-15, pripen18, pripen33 are added. These decribe pension payments to privately managed pension schemes.;*/

		data inpi&i;
			set inpi&i;
		%if &i < 2010 %then %do;
				pensindbetal = arbpen10+arbpen11+arbpen12+arbpen13+arbpen14+arbpen15+arbpen16+
					pripen10+pripen11+pripen12+pripen13+pripen14+pripen15;
				pripensindbetal = pripen10+pripen11+pripen12+pripen13+pripen14+pripen15;
			%end;
			%else %if &i >= 2010 and &i < 2013 %then %do;
				pensindbetal = arbpen10+arbpen11_2012+arbpen12+arbpen13+arbpen14+arbpen15+arbpen16+arbpen18+
					pripen10+pripen11_2012+pripen12+pripen13+pripen14+pripen15+pripen18;
				pripensindbetal = pripen10+pripen11_2012+pripen12+pripen13+pripen14+pripen15+pripen18;
			%end;
			%else %if &i >= 2013 %then %do;
				pensindbetal = arbpen10+arbpen11_2012+arbpen12+arbpen13+arbpen14+arbpen15+arbpen16+arbpen18+arbpen4445+
					pripen10+pripen11_2012+pripen12+pripen13+pripen14+pripen15+pripen18+pripen33;
				pripensindbetal = pripen10+pripen11_2012+pripen12+pripen13+pripen14+pripen15+pripen18+pripen33;
			%end;
			keep pnr pensindbetal pripensindbetal;
		run;
		
				%if &i >= 2008 %then %do; *�rstal skal �ndres i tilf�lde af genleverance af IDAP tilbage i tid;
			data idap&i;
			set idap&i;
			arledgr = LEDIGHED_BRUTTO / 52 * 1000; *arledgr is the share of year as unemployed (exists in raw data
			until 2007). Replaced by ledighed_brutto, which is the number of weeks in the year with gross
			unemployment (bruttoledighed);
			run;
		%end;
	%end;
%mend;

%reg_rette;

/************************************************************************
* 7) The register data is merged in individual data on CPR-level for 	*
*	 each year.															*
*																		*
*	 All flow variables (income etc.) in IND and IDAP indicates the		*
*	 total sized of the variable in the respective year. Assets and dept*
*	 data in IND are calculated at the end of the year.					*
*	 Information in BEF and BOL are calculated at the start of the year.*
*	 Therefore, the information from IND and IDAP for a given year are  *
*	 merged with BEF and BOL data from the following year. 				*
*	 Population (given from BEF) for a given year consists of all 		*
*	 persons with place of residence in Denmark per 1. January the		*
*	 following year.													*
*																		* 
* Note: Unlike the other status data, UDDA is calculated per 1. October *
*		Therefore, UDDA from the current year is employed.				*
/***********************************************************************/

/*The macro samle generates a data set, temp.samle&i, by merging BEF, IND, IDAP, UDDA, INHP, INPI, AKM
  SYHB with real estate assets and mortgage data*/
%macro samle;
	%do i = &startaar %to &slutaar;
	%let g = %eval(&i+2);
	%let j = %eval(&i+1);
	%let h = %eval(&i-1);
	%let k = %eval(&i-2);
		*Generates samle&i by merging BEF and IDAP for &aar, &aar+1, and &aar-1 with UDDA, INHP (tax liable (afgift) withdrawn pensions and 
		payouts), INPI (pension payouts), AKM, from &aar, and IND and SYHB (Hospital use) from &aar and &aar-1 by pnr;
		*Variables from prev. year are renamed with _lag or lag (1 and 2 if necessary);
		data temp.samle&i (drop=BOP_VFRA ejerforhold_char);
			merge raw.bef&j  (in=a keep= pnr koen alder familie_id familie_type kom plads civst aegte_id BOP_VFRA rename=(aegte_id=aegtefaelle_id))  
				raw.bef&i  (keep= pnr civst aegte_id familie_id rename=(civst=civstlag1 aegte_id=aegtefaelle_idlag1 familie_id=familie_idlag1))
				raw.bef&h  (keep= pnr civst aegte_id familie_id rename=(civst=civstlag2 aegte_id=aegtefaelle_idlag2 familie_id=familie_idlag2))
				raw.udda&i (keep= pnr hfaudd)
				work.ejerforhold&j (keep= pnr ejerforhold rename=(ejerforhold=ejerforhold_char))
				idap&i (keep= pnr arledgr ) 
				idap&h (keep= pnr arledgr rename=(arledgr=arledgrlag1))
				idap&k (keep= pnr arledgr rename=(arledgr=arledgrlag2)) 
				ind&i (keep= pnr arblhumv bankakt bankgaeld dispon_13 ejendomsvurdering erhvervsindk_13 formrest_ny05 formueindk_brutto indestpi 
					kursakt lejev_egen_bolig loenmv_13 netovskud_13 oblakt oblgaeld pantakt pantgaeld 
					penbpa penbpb pensblb2 pensblb7 perindkialt_13 
					qaktivf_ny05 qandpens qpassivn qrentind qtilpens qtjpens 
					rentudgpr rentudio rentupri rentvirk resuink_13 rntiovir rntudiov rntudstu rudgbank skatmvialt_13 
					udlakt udprigael)
				inhp&i (keep = pnr haevpen atp_engang atp_engangaf haevpenaf ld_udbetal ld_udbetalaf)
				inpi&i (in=h)
				akm&i (keep = pnr socio13)
				ind&h (keep= aktieindk bankakt bankgaeld dispon_13 indestpi kursakt lejev_egen_bolig oblakt oblgaeld pantakt pantgaeld pnr udlakt udprigael 
		          	rename= (bankakt=bankakt_lag bankgaeld=bankgaeld_lag dispon_13=dispon_13_lag indestpi=indestpi_lag kursakt=kursakt_lag
						lejev_egen_bolig=lejev_egen_bolig_lag oblakt=oblakt_lag oblgaeld=oblgaeld_lag pantakt=pantakt_lag
						pantgaeld=pantgaeld_lag udlakt=udlakt_lag udprigael=udprigael_lag)) 
 				syhb&i (keep= iants pdgs pnr)
				syhb&h (keep= pdgs pnr rename=(pdgs=pdgs_lag));

			by pnr;
			if a;
			aar=&i;
			/* Corrects familie_type, changed in BEF2016 */
			%if &i >= 2015 %then %do;
				if familie_type in (9, 10) then familie_type = 5;
				if familie_type in (7, 8) then familie_type = 1;
			%end;
			/* Corrects pension payments from . to 0. */
			/* Is generated in the macro "reg_rette" above */
			if pensindbetal = . then pensindbetal=0;
			if pripensindbetal =. then pripensindbetal=0;

			length ejerforhold 3.; *Type adjustment;
			ejerforhold=ejerforhold_char+0;
			length adraar 3.;
			adraar=YEAR(bop_vfra); 
		run &debug;

		/*Merges data med real estate assets from FORMEJER and FORMAND (exists from 2004)
		  The data set ejendomsformue&i is generated in the macro formue_ejendom, and is merged 
		  on samle&i (saved in the temp folder)*/
		%if &i >= 2004 %then %do;
			data temp.samle&i;
				merge temp.samle&i (in=a)
					ejendomsformue&i;
				by pnr;
				if a = 1; *Uses only observations from samle&i;
			run;
		%end;
		
		/* Merges  with mortgage data, Realkreditindberetninger, exists from 2009.
		   Split into 3, since some variables are included with 2 years lag from 2011*/
		*Merged onto samle&i;
		%if &i = 2009 %then %do;
			data temp.samle&i;
				merge temp.samle&i  (in = a)
						databib.rk_Individdata&i (in=b);
				by pnr;
				if a; *Only observations from samle;
				*Information for prev. years are set to missing in 2009;
				length realkredit 3.; IF b = 1 THEN realkredit = 1; else realkredit = 0;
				length realkredit_lag 3.; realkredit_lag = .;
				length realkredit_lag2 3.; realkredit_lag2 = .;
				hovedobl_lag = .;
				restobl_lag = .;
				rstjn105_lag = .;
				rstsep45_lag = .;
				afdrag_for_lag = .;
				rstjn105_lag2 = .;
				rstsep45_lag2 = .;
				afdrag_for_lag2 = .;
			run;
		%end;
		%if &i = 2010 %then %do;
			data temp.samle&i;
				merge temp.samle&i  (in = a)
					databib.rk_Individdata&i (in=b)
					databib.rk_Individdata&h (in=d keep=pnr rstjn105 rstsep45 afdrag_for restobl hovedobl
										rename=(rstjn105=rstjn105_lag rstsep45=rstsep45_lag afdrag_for=afdrag_for_lag restobl=restobl_lag hovedobl=hovedobl_lag));
				by pnr;
				if a; *Only observations from samle;
				*Generates the dummy realkredit equal to 1 if the observation matches (samle and rk_Individdata&i);
				*Generates the dummy realkredit_lag equal to 1 if the observation matches (samle and rk_Individdata&h, that is &aar-1);
				length realkredit 3.; IF b = 1 THEN realkredit = 1; else realkredit = 0;
				length realkredit_lag 3.; IF d = 1 THEN realkredit_lag = 1; else realkredit_lag = 0;
				*Variables for 2 lags are set missing;
				length realkredit_lag2 3.; realkredit_lag2 = .;
				rstjn105_lag2 = .;
				rstsep45_lag2 = .;
				afdrag_for_lag2 = .;
			run;
		%end;
		%if &i > 2010 %then %do;
			data temp.samle&i;
				merge temp.samle&i  (in = a)
					databib.rk_Individdata&i (in=b)
					databib.rk_Individdata&h (in=d keep=pnr rstjn105 rstsep45 afdrag_for restobl hovedobl
										rename=(rstjn105=rstjn105_lag rstsep45=rstsep45_lag afdrag_for=afdrag_for_lag restobl=restobl_lag hovedobl=hovedobl_lag))
					databib.rk_Individdata&k (in=f keep=pnr rstjn105 rstsep45 afdrag_for 
										rename=(rstjn105=rstjn105_lag2 rstsep45=rstsep45_lag2 afdrag_for=afdrag_for_lag2));
				by pnr;
				if a; *Uses only observations from samle;
				*Dummies for match between mortgage data and samle;
				length realkredit 3.; IF b = 1 THEN realkredit = 1; else realkredit = 0;
				length realkredit_lag 3.; IF d = 1 THEN realkredit_lag	= 1; else realkredit_lag = 0;
				length realkredit_lag2 3.; IF f = 1 THEN realkredit_lag2 = 1; else realkredit_lag2 = 0;
			run;
		%end;
		/* Inserts a blank data set for the year prior to 2009. */
		%if &i < 2009 %then %do;
			data temp.samle&i;
				merge temp.samle&i (in = a)
				rk_individdatadummy (in=b);
				by pnr;
				if a; *Uses only observations from samle;
				*The mortgage variables are set to missing;
				length realkredit 3.; Realkredit = .;
				length realkredit_lag 3.; realkredit_lag = .;
				length realkredit_lag2 3.; realkredit_lag2 = .;
				restobl_lag = .;
				hovedobl_lag = .;
				rstjn105_lag = .;
				rstsep45_lag = .;
				afdrag_for_lag = .;
				rstjn105_lag2 = .;
				rstsep45_lag2 = .;
				afdrag_for_lag2 = .;
		%end;
	%end;
%mend;
%samle;

/* Delets work data sets */
%macro slette_inpi_ind;
	%do i = &startaar_2 %to &slutaar;
		proc datasets library = work;
			delete inpi&i ind&i bol&i Bol_unik_&i Ejerforhold&i Ejf&i akm&i idap&i ejendomsformue&i Formejer&i Formand&i syhb&i inhp&i;
		run;
	%end;
%mend;
%slette_inpi_ind;

/************************************************************************
* 8) Data on individual level from each year are merged into one big    * 
*    data set on individual level: databib.Individdata.					*
* 																		*
* 
*	 Dummies for divorce and unemplyment within the last 24 months are  *
*	 computed.                            								*
************************************************************************/


*Appends samle data for all years;
data databib.Individdata /*&slutaar.*/ ;
	set temp.samle&startaar - temp.samle&slutaar;
		/* Set missing values to 0 where necessary*/
		if arledgr=0 OR arledgr=. 
			then beskaef=1; 
		else beskaef=0;
		if rente_afd_hyp_ret=. then rente_afd_hyp_ret=0;
		if rente_bet_nu=. then rente_bet_nu=0;

		if rentudgpr=. THEN rentudgpr=0;

		if qrentind=. THEN qrentind=0;

		if iants=. then iants=0;
		if pdgs=. then pdgs=0;
		if haevpen=. then haevpen=0;
		if haevpenaf=. then haevpenaf=0;
		
		if ATP_engang=. then ATP_engang=0;
		if ATP_engangaf=. then ATP_engangaf=0;
		if SP_pension=. then SP_pension=0;
		if SP_pension lt 0 and SP_pension ne . then SP_pension=abs(SP_pension);
		if SP_pensionaf=. then SP_pensionaf=0;
		if LD_udbetal=. then LD_udbetal=0;
		if LD_udbetalaf=. then LD_udbetalaf=0;
		  
		/* Dummy for divorce of death of spouse within the last two year*/
		length afbrudtaegteskab  3.;
		length afbrudtaegteskab1 3.;
		length afbrudtaegteskab2 3.;

		if (civst     in ("F","E","O","L") and civstlag1 in ("G","P")) 
			or (civst     in ("G","P") and aegtefaelle_idlag1 ne aegtefaelle_id     and aegtefaelle_idlag1 ne "")
			then afbrudtaegteskab1 = 1; 
		else afbrudtaegteskab1=0;

		if (civstlag1 in ("F","E","O","L") and civstlag2 in ("G","P")) 
			or (civstlag1 in ("G","P") and aegtefaelle_idlag2 ne aegtefaelle_idlag1 and aegtefaelle_idlag2 ne "")
			then afbrudtaegteskab2 = 1; 
		else afbrudtaegteskab2=0;

		if afbrudtaegteskab1=1 or afbrudtaegteskab2=1 
			then afbrudtaegteskab=1; 
		else afbrudtaegteskab=0;

		label afbrudtaegteskab1 = 'Marriage ended within the latest year (divorce of death of spouse)';
		label afbrudtaegteskab2 = 'Marriage ended previous year (divorce of death of spouse)';
		label afbrudtaegteskab  = 'Marriage ended within the latest 2 year (divorce of death of spouse)';

		/* Dummies for at least 6 months unemployment in the past 24 months.*/
		arledgr24 = arledgr+arledgrlag1;
		
		*Format of dummies;
		length ledig1     3.;
		length ledig2     3.;
		length ledig24mnd 3.;

		if arledgr     >= 250 
			then ledig1     = 1; 
		else if arledgr     < 250 
			then ledig1     = 0;
		if arledgrlag1 >= 250 
			then ledig2     = 1; 
		else if arledgrlag1 < 250 
			then ledig2     = 0;
		if arledgr24   >= 500 
			then ledig24mnd = 1; 
		else if arledgr24   < 500 
			then ledig24mnd = 0;
	
		label ledig1			= 'More than 3 months unemployment, current year'
		      ledig2			= 'More than 3 months unemployment, last year'
		      ledig24mnd		= 'More than 3 months unemployment, last 2 years'
			  indestpi_lag		= 'Deposits in credit institutions etc., prev. year.'
		      bankakt_lag		= 'Deposits in credit institutions, prev. year.'
		      oblakt_lag		= 'Market value (Kursv�rdi) of bond assets, prev. year'
		      pantakt_lag		= 'Market value (Kursv�rdi) of mortgage deeds in escrow, prev. year'
			  kursakt_lag		= 'Market value of Danish shares and investment certificate, prev. year'
			  udlakt_lag		= 'Foreign assets, prev. year'
			  oblgaeld_lag		= 'Market value of bond dept, prev. year'
			  bankgaeld_lag		= 'Dept to credit institutions, prev. year'
			  pantgaeld_lag		= 'Dept of mortgage deeds in escrow, prev. year'
			  udprigael_lag		= 'Dept to foreign mortgage institutions etc., prev. year'
			  restobl_lag		= 'Outstanding dept to mortgage prev. year, mortgage data (realkreditdata)'
			  rstjn105_lag		= '105 days arrears on June term, prev. year'
		      rstsep45_lag		= '45 days arrears on September term, prev. year'
			  rstjn105_lag2		= '105 days arrears on June term, 2 years before'
		      rstsep45_lag2		= '45 days arrears on September term, 2 years before'
		      dispon_13_lag		= 'Disposiable income, prev. year'
			  lejev_egen_bolig_lag		= 'Rent value of own property, prev. year'
			  afdrag_for_lag	= 'Repayments on mortgage dept, prev. year'
			  afdrag_for_lag2	= 'Repayments on mortgage dept, 2 years before'
		      realkredit		= 'Mortgage client, end of year'
			  realkredit_lag	= 'Mortgage client, end of prev. year'
		      realkredit_lag2	= 'Mortgage client, end of year 2 years before'
		      adraar			= 'Year of relocation to the adress'
			  pdgs_lag			= 'Number of days hospitalized (disease), prev. year';

		length alder familie_type koen plads socio13 afdragsfrihed_for afdragsfrihed_nu 3.;
run &debug;

/************************************************************************
*	 	DATABIB.INDIVIDDATA IS GENERATED 								*
************************************************************************/

/* Delets data sets in temp library */
%macro slette_samle;
	%do i = &startaar %to &slutaar;
		proc datasets library = temp;
			delete samle&i;
		run;
	%end;
%mend;
%slette_samle;


/************************************************************************
* 9) Individual data are summarized to a temporary family data set		*
* 	 
* 	In the proc means below is a new data set formed. Income, dept and  *
*	information on assets are aggregated over family members. 			*
*	Input variables listes in sumvariable, maxvariable and minvariable.	*
*	Variable to be summarized over family members, should be placed		*
*	under sumvariable. 													*
*	If max amoung the family members is wanted, the variable should be 	*
*	placed under maxvariable. Same goes for min and minvariable.		*
*																		*
*	The new family variables are named after the corresponding			*
*	variables for the individuals with the addendum "fam".  			*
*	The naming is done in the macros sumnavn, maxnavn, minnavn for 		*
*	summarized, max and min variables respectively. The new family 		*
*	are named as described prev. but with the extension _sum, _max, _min*
*																		*                                                                            
************************************************************************/

* Sorts the data set after year, familie_id and pnr and saves a temporary data set samle_pnr_famsort;

data temp.samle_pnr_famsort;
set databib.Individdata;
keep aktieindk		arblhumv				ejendomsvurdering 	
					dispon_13 				erhvervsindk_13 		formrest_ny05 					
					haevpen					haevpenaf				atp_engang				atp_engangaf			ld_udbetal 
					ld_udbetalaf			sp_pension				sp_pensionaf
    				loenmv_13				netovskud_13			lejev_egen_bolig		perindkialt_13
					qaktivf_ny05			qpassivn				resuink_13				skatmvialt_13 			bankakt 				
					bankgaeld 
					indestpi 				kursakt 				oblakt 					oblgaeld 				pantakt 
					pantgaeld 				rentudgpr				rentudio				rentupri 				rentvirk
					qrentind
					rntiovir 				rntudiov 				rntudstu 				rudgbank 				udlakt 
					udprigael 				pensindbetal 			pripensindbetal 		qandpens 				qtilpens 
					qtjpens 				indestpi_lag 			bankakt_lag
					oblakt_lag				pantakt_lag				kursakt_lag				udlakt_lag				oblgaeld_lag
				    bankgaeld_lag			pantgaeld_lag 			udprigael_lag		    hovedobl            	hovedknt
					restobl             	restknt       			restobl_afdfri			restobl_rtl				restobl_80
					restobl_FastRente		restobl_VarRente		restobl_F5				restobl_GE_F5		 	hovedobl_lag
	 				afdragsfrihed_for   	afdragsfrihed_nu        boligvaerdi         	boligvaerdi_samlet		andboligvaerdi         
     				rentetilpas				fastrente 				KUN_rentetilpas 	    KUN_afdragsfrihed 		KUN_fastrente
					rentetilpas_iaar		F1                  	F3					 	F5						                  
					optag0310 				optag0210
     				afdrag_for          	afdrag_nu               rente_bet_for           rente_bet_nu			bidrag					  
	 				frivrd_alle		   		frivrd_ejer			
	 				dispon_13_lag       	lejev_egen_bolig_lag    afdrag_for_lag          afdrag_for_lag2
     				realkredit          	realkredit_lag          realkredit_lag2			boligvaerdi_real		restobl_lag
					penbpa					penbpb					pensblb2				pensblb7
maxLTV            		senest_optag			senest_optag_aar_maaned senest_optag_ejer		senest_optag_restance	
					senest_optag_res_ejer	antalejerbol			andelsbolig
					
	tidligst_optag			tidligst_optag_ejer		adraar aar familie_id pnr plads koen alder ejerforhold familie_type civst socio13 hfaudd perindkialt_13 erhvervsindk_13 
				loenmv_13 netovskud_13 aktieindk afbrudtaegteskab ledig24mnd
				afbrudtaegteskab1 afbrudtaegteskab2 ledig1 ledig2 iants pdgs pdgs_lag;
run;
proc sort data=temp.samle_pnr_famsort ;
	by aar familie_id pnr;
run &debug;

%let sumvariable = 	aktieindk				arblhumv				ejendomsvurdering 	
					dispon_13 				erhvervsindk_13 		formrest_ny05 						
					haevpen					haevpenaf				atp_engang				atp_engangaf			ld_udbetal 
					ld_udbetalaf			sp_pension				sp_pensionaf
    				loenmv_13				netovskud_13			lejev_egen_bolig		perindkialt_13
					qaktivf_ny05			qpassivn				resuink_13				skatmvialt_13 			bankakt 				bankgaeld 
					indestpi 				kursakt 				oblakt 					oblgaeld 				pantakt 
					pantgaeld 				rentudgpr				rentudio				rentupri 				rentvirk
					qrentind
					rntiovir 				rntudiov 				rntudstu 				rudgbank 				udlakt 
					udprigael 				pensindbetal 			pripensindbetal 		qandpens 				qtilpens 
					qtjpens 				indestpi_lag 			bankakt_lag
					oblakt_lag				pantakt_lag				kursakt_lag				udlakt_lag				oblgaeld_lag
				    bankgaeld_lag			pantgaeld_lag 			udprigael_lag		    hovedobl            	hovedknt
					restobl             	restknt       			restobl_afdfri			restobl_rtl				restobl_80
					restobl_FastRente		restobl_VarRente		restobl_F5				restobl_GE_F5		
	 				afdragsfrihed_for   	afdragsfrihed_nu        boligvaerdi         	boligvaerdi_samlet		andboligvaerdi         
     				rentetilpas				fastrente 				KUN_rentetilpas 	    KUN_afdragsfrihed 		KUN_fastrente
					rentetilpas_iaar		F1                  	F3					 	F5						                  
					optag0310 				optag0210
     				afdrag_for          	afdrag_nu               rente_bet_for           rente_bet_nu			bidrag					  
	 				frivrd_alle		   		frivrd_ejer			
	 				dispon_13_lag       	lejev_egen_bolig_lag    afdrag_for_lag          afdrag_for_lag2
     				realkredit          	realkredit_lag          realkredit_lag2			boligvaerdi_real		restobl_lag
					penbpa					penbpb					pensblb2				pensblb7
;

%let maxvariable = 	maxLTV            		senest_optag			senest_optag_aar_maaned senest_optag_ejer		senest_optag_restance	
					senest_optag_res_ejer	antalejerbol			andelsbolig
					
;

%let minvariable = 	tidligst_optag			tidligst_optag_ejer		adraar
;

%macro sumnavn(varliste);
	%let k=1;
	%let variabel=%scan(&varliste, &k);
	%do %while("&variabel" ne "");
		sum(&variabel)=fam&variabel
		%let k=%eval(&k+1);
		%let variabel=%scan(&varliste, &k);
	%end;
%mend sumnavn;

%macro maxnavn(varliste);
	%let k=1;
	%let variabel=%scan(&varliste, &k);
	%do %while("&variabel" ne "");
		max(&variabel)=fam&variabel
		%let k=%eval(&k+1);
		%let variabel=%scan(&varliste, &k);
	%end;
%mend maxnavn;

%macro minnavn(varliste);
	%let k=1;
	%let variabel=%scan(&varliste, &k);
	%do %while("&variabel" ne "");
		min(&variabel)=fam&variabel
		%let k=%eval(&k+1);
		%let variabel=%scan(&varliste, &k);
	%end;
%mend minnavn;


proc means data=temp.samle_pnr_famsort noprint;
	var &sumvariable &maxvariable &minvariable;
	by aar familie_id;
	output out=temp.fam1 
		%sumnavn(&sumvariable)
		%maxnavn(&maxvariable)
		%minnavn(&minvariable)
	/keeplen autolabel;
run &debug;

/************************************************************************
* 10) Register data from the family income register, FAIK, and stamdata *
*	  on families, FAM, are cleaned.									*
************************************************************************/

%macro faik_fam;
	%do i = &startaar %to &slutaar;
	%let j=%eval(&i+1);
		*Merges FAIK and FAM;
		%if &i < 2015 or &i=2017 %then %do; 
			data faik&i;
				merge raw.faik&i
					raw.fam&j (rename=(antboernf=famantboernf aldaeldst=famaldaldbf aldyngst=famaldyngbf));;
				by familie_id;
				famhoejstudda_1 = famhoejstudda * 1;
				drop famhoejstudda;
				rename famhoejstudda_1 = FAMHOEJSTUDDA;
				kom_1 = kom*1;
				drop kom;
				rename kom_1 = kom;
				aar=&i;
			run;
		%end;
		%if &i = 2015 or &i =2016 %then %do;
			data faik&i; *Changes format;
				merge raw.faik&i
					raw.fam&j (rename=(antboernf=famantboernf aldaldbf=famaldaldbf aldyngbf=famaldyngbf));
				by familie_id;
				aar=&i;
			run;
		%end;
		%if &i = 2016 %then %do;
			data faik&i;
			merge raw.faik&i
					raw.fam&j (rename=(antboernf=famantboernf aldaldbf=famaldaldbf aldyngbf=famaldyngbf));
				by familie_id;							        		   
				aar=&i;
				famhoejstudda_1 = famhoejstudda * 1;
				drop famhoejstudda;
				rename famhoejstudda_1 = FAMHOEJSTUDDA;
				kom_1 = kom*1;
				drop kom;
				rename kom_1 = kom;
			run;
		%end;
	%end;
%mend;
%faik_fam;

*Appends the prev. merged data sets to samle_faik;
data samle_faik;
	set faik&startaar - faik&slutaar;
run &debug;

/* Delets work data sets  */
%macro slette_faik;
	%do i = &startaar %to &slutaar;
		proc datasets library = work;
			delete faik&i;
		run;
	%end;
%mend;
%slette_faik;

/**************************************************************************
* 11) Data on familt car assets, FORMBIL, and information on car purchase *
*	  are cleaned														  *
**************************************************************************/

%macro bilformue;
	%do i=2004 %to &slutaar; *FORMBIL first computed in 2004;
		data formbil&i; 
			set raw.formbil&i;
			keep aar familie_id vejledende_salgspris;
			rename vejledende_salgspris = bilvaerdi;
			aar=&i;
		run;
	%end;

%mend;
%bilformue;
*Appends data sets to bilformue;
data bilformue; 
	set formbil2004 - formbil&slutaar;
run;

/* Deletes work data sets */
%macro slette_faik_bil;
	%do i = &startaar %to &slutaar;
		proc datasets library = work;
			delete faik&i formbil&i;
		run;
	%end;
%mend;
%slette_faik_bil;


%macro bil;
%do i=2003 %to &slutaar;
%let j=%eval(&i+1);
data bil&i; set raw.dmrb&i (keep=ejer_pnr E_GYLDIGFRA STANDARDPRISDKK FOERSTEREGISTRERINGDATO);
if ejer_pnr ~=.; *Keeps only cars owned by persons with a pnr;
aar = &i;

/*If a person is registered as owner during a year, the car is counted as bought in that year.
A sold car is not included in the family's assets the year, it is being sold.
Cars bought snd sold the same year are not included. 
Change of ownership within the same family is accounted for in the same manner as a normal (used) car 
(the same principle as for house sales).
The value at the end of the year (ultimo) is gross (brutto), that is indicative sales price (cash value). 
See the variable description in teamshare (vejledende salgspris)*/

* Changes format;
pnr=ejer_pnr+0;

aar = &i;
bilalder = aar - year(FOERSTEREGISTRERINGDATO); *Age of the car;

kobtnybil=0;
if E_GYLDIGFRA=FOERSTEREGISTRERINGDATO AND year(E_GYLDIGFRA) = aar THEN kobtnybil = 1; *Dummy for new car being bought this year;

kobtbrugtbil=0;
if E_GYLDIGFRA>FOERSTEREGISTRERINGDATO AND year(E_GYLDIGFRA) = aar THEN kobtbrugtbil = 1; *Dummy for used car being bought this year;

if kobtnybil = 1 OR kobtbrugtbil = 1 then kobtbilvaerdi = STANDARDPRISDKK; 
run;

proc sort data=bil&i; by pnr; run;

data bef&j; set raw.bef&j;
pnr2=pnr+0; *changes format;
drop pnr;
rename pnr2=pnr;
*Merges the generated data set bil&i with BEF from the following year (same procedure as before);
data bil&i; 
merge 	bil&i (in=a)
		bef&j (in=b keep=pnr familie_id);
		by pnr;
if b; *Keeps only observations from BEF;
run;

proc sort data=bil&i; by familie_id; run;

*Generates the data set bildata&i by a proc menans on bil&i;
proc means data=bil&i noprint;
var bilalder kobtnybil kobtbrugtbil STANDARDPRISDKK;
by familie_id;
*Names the output variables, generates sums and means;
output out=bildata&i mean(bilalder)=bilalder N(bilalder)=bilantal sum(kobtnybil)=kobtnybil sum(kobtbrugtbil)=kobtbrugtbil 
mean(STANDARDPRISDKK)=bilvaerdi_gns sum(STANDARDPRISDKK)=bilvaerdi_sum sum(kobtbilvaerdi)=kobtbilvaerdi; 
run;

data bildata&i; set bildata&i;
aar = &i;
drop _TYPE_ _FREQ_;
run;

%end;
%mend bil; 
%bil;

*Appends all bildata data sets and sets missing variables to 0.
Generates dummy kobtbil (bought a car) for buying a car in current year;
data databib.bildata;
set bildata2003 - bildata&slutaar;
if kobtnybil=. then kobtnybil=0;
if kobtbrugtbil=. then kobtbrugtbil=0;
kobtbil=0;
if kobtnybil>=1 or kobtbrugtbil>=1 then kobtbil=1;
run;

*Sorts by year and family id number;
proc sort data=databib.bildata;
by aar familie_id;
run;


/************************************************************************
* 12) Data set on families' pension assets, PENSFORM, are cleaned		*
*	  Data set with pension assets are saved in the databib-folder		*
************************************************************************/

%macro pension;
	%DO aar=2014 %TO &slutaar;
	%let aar1 = %eval(&aar + 1); 

		/* The distrubtued reserves are not distributed on tax codes. 
		   The same amount can therefore exists several time within the same contract. In the following,
		   are the 3 variables (for distrubuted reserves) distrubted on tax codes with the sole purpose of summarizing 
		   over contracts.
	 */

	*saves sorted version of pensform in work library;
		PROC SORT DATA=raw.pensform&aar OUT=pension&aar;
			BY kontraktnr pnr;
		RUN;

		DATA pension_andel&aar;
			SET pension&aar;
			BY kontraktnr;
			count+1;
			IF first.kontraktnr THEN count=1;
		RUN;

		DATA pension_andel_2&aar;
			SET pension_andel&aar;
			BY kontraktnr;
			andel_kon=1/count;
			IF last.kontraktnr THEN output;
		RUN;

		DATA pension_korr&aar;
			MERGE pension&aar (in=a)
				  pension_andel_2&aar (KEEP=kontraktnr andel_kon);
			BY kontraktnr;

			/* Corrects distributed reserves, so they can be summarized*/
			ANDAKKRGUBLB_andel=ANDAKKRGUBLB*andel_kon;
			ANDKOLBONUSBLB_andel=ANDKOLBONUSBLB*andel_kon;
			ANDSAEBONUSBLB_andel=ANDSAEBONUSBLB*andel_kon;

			fordelte_reserver=ANDAKKRGUBLB_andel+ANDKOLBONUSBLB_andel+ANDSAEBONUSBLB_andel;

			/* Corrects pensions schemes(pensionsordninger), so they are calculated after tax*/
			IF CPSSKATKOD in ('33','44','45','81','82') THEN Pensdepotblb_beskat=PENSDEPOTBLB; /* Tax codes indicates schemes, which are not taxed when disbursed (udbetalt) */
			ELSE Pensdepotblb_beskat=PENSDEPOTBLB*0.6;
			LABEL Pensdepotblb_beskat ='Pensions deposit after tax';
			RUN;

		PROC SORT DATA=pension_korr&aar;
			BY pnr;
		RUN; 

		/* Adds familie_id to summarize pension on family level*/
		DATA pension_fam2_&aar;
			MERGE pension_korr&aar (in=a)
				  raw.bef&aar1 (KEEP= pnr familie_id);
			BY pnr;
			IF a; *Keeps only observations from pension_korr&aar;
		RUN;

		PROC SORT DATA=pension_fam2_&aar;
			BY familie_id SSKTYPKOD;
		RUN;

		/* Summarize variables on family level and SSKTYPKOD, generates data set pension_fam&aar */
		PROC MEANS DATA=pension_fam2_&aar SUM N NOPRINT;
			VAR fordelte_reserver Pensdepotblb_beskat GARANYDELSBLB;
			BY familie_id SSKTYPKOD; *SSKTYPKOD is the code for type of company, indicating in which type of company the pension scheme is created in;
			OUTPUT OUT=pension_fam&aar (DROP= _FREQ_ _TYPE_)
			SUM(fordelte_reserver Pensdepotblb_beskat GARANYDELSBLB) = fordelte_reserver Pensdepotblb_beskat GARANYDELSBLB;
		RUN;

		/* Changes order of the data, such that there is only one row per family.
			Uncommented lines indicates that the variables is not created for the type of company */
		DATA databib.pension&aar (DROP=SSKTYPKOD);
			MERGE pension_fam&aar (where=(SSKTYPKOD='A') RENAME=(fordelte_reserver=fordelte_reserver_A 
															  /*Pensdepotblb_beskat=Pensdepotblb_beskat_A*/
															  GARANYDELSBLB=GARANYDELSBLB_A))
				  pension_fam&aar (where=(SSKTYPKOD='F') RENAME=(/*fordelte_reserver=fordelte_reserver_F*/ 
															  Pensdepotblb_beskat=Pensdepotblb_beskat_F
															  GARANYDELSBLB=GARANYDELSBLB_F))
				  pension_fam&aar (where=(SSKTYPKOD='L') RENAME=(fordelte_reserver=fordelte_reserver_L 
															  Pensdepotblb_beskat=Pensdepotblb_beskat_L
															  GARANYDELSBLB=GARANYDELSBLB_L))
				  pension_fam&aar (where=(SSKTYPKOD='P') RENAME=(/*fordelte_reserver=fordelte_reserver_P */
															  Pensdepotblb_beskat=Pensdepotblb_beskat_P
															  /*GARANYDELSBLB=GARANYDELSBLB_P*/))
				  pension_fam&aar (where=(SSKTYPKOD='T') RENAME=(/*fordelte_reserver=fordelte_reserver_T */
															  Pensdepotblb_beskat=Pensdepotblb_beskat_T
															  /*GARANYDELSBLB=GARANYDELSBLB_T*/))
				  ;
			BY familie_id;
			LABEL fordelte_reserver_A='Distributed reserves, company type A' 
				  /*Pensdepotblb_beskat_A='Pension deposits, company type A'*/
				  GARANYDELSBLB_A='Guaranteed output, company type A'

				  /*fordelte_reserver_F='Distributed reserves, company type F' */
				  Pensdepotblb_beskat_F='Pension deposits, company type F'
				  GARANYDELSBLB_F='Guaranteed output, company type F'

				  fordelte_reserver_L='Distributed reserves, company type L' 
				  Pensdepotblb_beskat_L='Pension deposits, company type L'
				  GARANYDELSBLB_L='Guaranteed output, company type L'

				  /*fordelte_reserver_P='Distributed reserves, company type P' */
				  Pensdepotblb_beskat_P='Pension deposits, company type P'
				  /*GARANYDELSBLB_P ='Guaranteed output, company type P'*/

				  /*fordelte_reserver_T='Distributed reserves, company type T' */
				  Pensdepotblb_beskat_T='Pension deposits, company type T'
				  /*GARANYDELSBLB_T='Guaranteed output, company type T'*/
				  ;
			fr_skat_A = 0.6*fordelte_reserver_A;
			fr_skat_L = 0.6*fordelte_reserver_L;
			pension_skat = sum(fr_skat_A,fr_skat_L,Pensdepotblb_beskat_F,Pensdepotblb_beskat_L,Pensdepotblb_beskat_P,Pensdepotblb_beskat_T);
			aar = &aar;
		RUN;

		PROC TABULATE DATA=pension_fam&aar missing format=comma32.0;
			VAR fordelte_reserver Pensdepotblb_beskat;
			CLASS SSKTYPKOD;
			TABLE SSKTYPKOD, fordelte_reserver Pensdepotblb_beskat;
		RUN;
		
%END;
%mend;
%pension;


*Appends pension&aar to a complete data set pension;
data pension;
	set databib.pension2014 - databib.pension&slutaar;
	keep familie_id pension_skat aar;
	if familie_id > 0;
run; 

/* Deletes work data sets */
proc datasets library = work;
	delete pension2014 - pension&slutaar Pension_andel2014-Pension_andel&slutaar Pension_andel_22014-Pension_andel_2&slutaar 
			Pension_fam2014-Pension_fam&slutaar Pension_fam2_2014-Pension_fam2_&slutaar pension_korr2014-pension_korr&slutaar;
run;


/************************************************************************
* 13) The family data set is merged with FAIK and FORMBIL. 				*
*	  Additional information on pension savings and car purchase are 	*
*	  added. Variable for the main person of the family (and potential 	*
*	  partner) are computed afterwards.									*
*	  Last, a dummy for the family being involved in real estate in the	*
*	  respective year (from 0a_ChangeOfProperty).						*
*																		*
*	  In the following data step s series of information is merged:		*
*	  1. Income, assets, and dept on family level. From the data fam1	*
*		 computed above.												*
*	  2. Addtional information from FAIK, from samle_faik computed above*
*	  3. Information on the individual on the identifying person of the	*
*		 family (main person). Determined by the variable PLADS (BEF).	*
*		 The variable is taken from the individual data set 			*
*	 	 sample_pnr_famsort from prev. (work library).					*
*	  4. Information on a potential partner (to the main person), 		*
*		 determined from the variable PLADS, is added. Data is taken 	*
*		 from samle_pnr_famsort. In families with singles, these will	*
*		 be missing.													*
*	  5. Dummies for, if the family has been ivolved in real estate		*
*		 in the respective year. These information is taken from the 	*	
*		 data sets nfn.dummyforhuskob og samle.famejendomshandler.		*
*		 The variables from the latter is the most reliable, and should *
*		 thus be used. samlefamejendomshandler is generated in the code:*
*		 0a_ChangeOfProperty											*
*	  6. Information on purchase of both new and used cars (including	*
*		 the total value (end of year) of the family's car fleet is 	*
*		 added 															*
************************************************************************/

data temp.familiedata_pre (drop= plads_1 plads_2);
	merge temp.fam1 (in=a )
		samle_faik
		pension
		temp.samle_pnr_famsort (keep= aar familie_id pnr plads koen alder ejerforhold familie_type civst socio13 hfaudd perindkialt_13 erhvervsindk_13 
				loenmv_13 netovskud_13 aktieindk afbrudtaegteskab ledig24mnd
				afbrudtaegteskab1 afbrudtaegteskab2 ledig1 ledig2 iants pdgs pdgs_lag
			where=(plads_1=1)
			rename=(pnr=pnr_1 plads=plads_1 koen=koen_1 alder=alder_1 civst=civst_1 socio13=socio13_1 hfaudd=hfaudd_1 perindkialt_13=perindkialt_13_1
				erhvervsindk_13=erhvervsindk_13_1 loenmv_13=loenmv_13_1 netovskud_13=netovskud_13_1 
				afbrudtaegteskab=afbrudtaegteskab_1 afbrudtaegteskab1=afbrudtaegteskab1_1 afbrudtaegteskab2=afbrudtaegteskab2_1 
				ledig1=ledig1_1 ledig2=ledig2_1 ledig24mnd=ledig24mnd_1 iants=iants_1 pdgs=pdgs_1 pdgs_lag=pdgs_lag_1))
		temp.samle_pnr_famsort (keep= aar familie_id pnr plads koen alder civst socio13 hfaudd perindkialt_13 erhvervsindk_13 
				loenmv_13 netovskud_13 aktieindk afbrudtaegteskab ledig24mnd
				afbrudtaegteskab1 afbrudtaegteskab2 ledig1 ledig2 iants pdgs pdgs_lag
			where=(plads_2=2)
			rename=(pnr=pnr_2 plads=plads_2 koen=koen_2 alder=alder_2 civst=civst_2 socio13=socio13_2 hfaudd=hfaudd_2 perindkialt_13=perindkialt_13_2
				erhvervsindk_13=erhvervsindk_13_2 loenmv_13=loenmv_13_2 netovskud_13=netovskud_13_2 
				afbrudtaegteskab=afbrudtaegteskab_2 afbrudtaegteskab1=afbrudtaegteskab1_2 afbrudtaegteskab2=afbrudtaegteskab2_2 
				ledig1=ledig1_2 ledig2=ledig2_2 ledig24mnd=ledig24mnd_2 iants=iants_2 pdgs=pdgs_2 pdgs_lag=pdgs_lag_2))
		databib.famejendomshandler
		databib.bildata 	
		bilformue;
		by aar familie_id;
		if a;
		label pnr_1					= 'Personal number, identifying person'
		      koen_1				= 'Gender, ident. person'
		      alder_1				= 'Age end of year, ident. person'
			  civst_1				= 'Marital status, ident. person'
			  socio13_1				= 'Socio-economic classification, ident. person'
		      hfaudd_1				= 'Highest completed education, ident. person'
		      perindkialt_13_1		= 'Total personal income, excl. computed rent value of own property and before deduction of interest expenses, ident. person'
		      erhvervsindk_13_1		= 'Business income, salary and net profit of self-employment incl. some fees, ident. person'
		      loenmv_13_1			= 'Total salary, ident. person'
		      netovskud_13_1		= 'Net profits from self-employment, before capital income and expenses, ident. person'
		      discoalle_indk_1		= 'Classification of occupation for primary employee occupation, current year, ident. person'
			  afbrudtaegteskab_1	= 'Marriage ended due to divorce or death of spouse within the past 2 years, ident. person'
		      afbrudtaegteskab1_1	= 'Marriage ended due to divorce or death of spouse within the past year, ident. person'
		      afbrudtaegteskab2_1	= 'Marriage ended due to divorce or death of spouse within the year before the current, ident. person'
		      ledig1_1				= 'Dummy for more than 3 months unemployment within the past year, ident. person'
		      ledig2_1				= 'Dummy for more than 3 months unemployment in the previous year, ident. person'
			  ledig24mnd_1			= 'Dummy for more than 6 months unemployment in the past 24 months, ident. person'
			  iants_1				= 'Number of hospitalizations due to disease, ident. person'
			  pdgs_1				= 'Days hospitalized due to disease, ident. person'
			  pdgs_lag_1			= 'Days hospitalized due to disease, previous year, ident. person'
		      pnr_2					= 'Personal number, partner'
		      koen_2				= 'Gender, partner'
		      alder_2				= 'Age end of year, partner'
			  civst_2				= 'Marital status, partner'
			  socio13_2				= 'Socio-economic classification, partner'
			  hfaudd_2				= 'Highest completed education, partner'
			  perindkialt_13_2		= 'Total personal income, excl. computed rent value of own property and before deduction of interest expenses, partner'
		      erhvervsindk_13_2		= 'Business income, salary and net profit of self-employment incl. some fees, partner'
		      loenmv_13_2			= 'Total salary, partner'
		      netovskud_13_2		= 'Net profits from self-employment, before capital income and expenses, partner'
		      discoalle_indk_2		= 'Classification of occupation for primary employee occupation, current year, partner'
		      afbrudtaegteskab_2	= 'Marriage ended due to divorce or death of spouse within the past 2 years, partner'
		      afbrudtaegteskab1_2	= 'Marriage ended due to divorce or death of spouse within the past year, partner'
		      afbrudtaegteskab2_2	= 'Marriage ended due to divorce or death of spouse within the year before the current, partner'
		      ledig1_2				= 'Dummy for more than 3 months unemployment within the past year, partner'
		      ledig2_2				= 'Dummy for more than 3 months unemployment in the previous year, partner'
			  ledig24mnd_2			= 'Dummy for more than 6 months unemployment in the past 24 months, partner'
			  iants_2				= 'Number of hospitalizations due to disease, partner'
			  pdgs_2				= 'Days hospitalized due to disease, partner'
			  pdgs_lag_2			= 'Days hospitalized due to disease, previous year, partner'
			  bilalder				= 'Average age of cars'
			  bilantal				= 'Number of cars'
			  kobtnybil				= 'Number of new cars bought in current year'
			  kobtbrugtbil			= 'Number of used cars bought in current year'
			  bilvaerdi_gns			= 'Average value of cars registered (fist time) after 2012'
			  kobtbilvaerdi			= 'Value of cars bought current year (reg. first time after 2012)'
			  kobtbil				= 'Dummy for car bought in current year'
			  ;
run &debug;

/* Deletes work data sets */
proc datasets library = work;
	delete samle_faik pension bilformue;
run;
proc datasets library = temp;
	delete samle_pnr_famsort fam1;
run;


/************************************************************************
* 14) Lastly, a series of relevant variables concerning the families	*
*	  are constructed, and the final data set on family level is 		*
*	  generated: databib.familiedata.									*
*																		*
*     Below, variables concerning assets and income are computed.		*
************************************************************************/ 

data databib.familiedata_2019;
	merge temp.familiedata_pre (in = a);
	by aar familie_id;
	if a;

	/*Computes total assets based on the market valuation of properties (instead of public assessment)*/
	if famboligvaerdi = . & aar >= 2004 then famboligvaerdi = 0;	 
	if famandboligvaerdi = . & aar >= 2004 then famandboligvaerdi = 0;	 
 
	if aar >= 2004 then famnyformue = famqaktivf_ny05 - famejendomsvurdering + famboligvaerdi + famandboligvaerdi;
	if aar >= 2004 then famnyformue_net = famnyformue - famqpassivn;

	/* Computes value of the family's assets, excl. the value of Danish property*/
	famandreaktiver = famqaktivf_ny05 - famejendomsvurdering;
	famandreaktiver_pens = famandreaktiver + pension_skat;

	label famnyformue          	= 'Total assets, real estate at market value'
	      famnyformue_net      	= 'Net assets, real estate at market value'
		  famandreaktiver      	= 'Total assets, excl. real estate in Denmark'
	      famandreaktiver_pens 	= 'Total assets, incl. pensions, excl. real estate in Denmark'

		  famandboligvaerdi		= 'Real estate assets, cooperative'
		  famboligvaerdi 		= 'Real estate assets, owned housing'
		  famboligvaerdi_samlet = 'Total real estate assets (owned and cooperative)';


	/* Computes disposable income, net dept ratio (nettog�ldskvote), gross dept ratio (bruttog�ldskvote),
		  interest burden (rentebyrde) and pension asstes ratio (pensionsformuekvote) for the family */

	/* Disposable income after tax and interest expenses, excl. rent value of own property*/
	famdispindk = famdispon_13 - famlejev_egen_bolig;
	/* disposable income for the adults members of the family, prev. year*/
	famdispindk_lag = famdispon_13_lag - famlejev_egen_bolig_lag;
	/*Income after tax, excl. rent value of own property*/
	famindkefterskat = famperindkialt_13 - famskatmvialt_13;
	/*Income before tax, excl. pensions payments (pensionsudbetalinger) from life insurance companies (livforsikringsselskaber), 
	pension funds (pensionskasser), annuity pension scheme (ratepensioner) etc. and ATP.*/
	famindkekspens = famperindkialt_13 - famqandpens - famqtilpens;

	/* Dept ratios: Dept (gross or net) in percent of income after tax*/
	/* Pension asset ratio: Pension assets (after tax) in percent of income after tax*/
	/* Interest burden: Interest expenses in percent of incom after tax*/

	/* The family's other dept (mortgage aside)*/
	famandengaeld = famqpassivn - famoblgaeld;
	label famandengaeld = 'Family�s other dept (total dept - mortgage)';

	if famindkefterskat > 0 then do;
		famnetgaeldkvote = -famnyformue_net / famindkefterskat * 100;
		famnetgaeldkvote_ubolig = -(famandreaktiver - famqpassivn)/ famindkefterskat * 100;
		famnetgaeldkvote_uandre = -(famboligvaerdi_samlet - famqpassivn) / famindkefterskat * 100;

		fambrugaeldkvote = famqpassivn / famindkefterskat * 100;
		famrentebyrde    = famrentudgpr / famindkefterskat * 100;
		famandengaeldkvote = famandengaeld / famindkefterskat * 100;
		famrealkgaeldkvote = famoblgaeld/ famindkefterskat * 100;
		fampenskvote  = pension_skat / famindkefterskat * 100;
	end;
	else if famindkefterskat le 0 then do;
		if famnyformue_net  < 0 
			then famnetgaeldkvote = 9999; 
		else famnetgaeldkvote = 0;
		if famqpassivn      > 0 
			then fambrugaeldkvote = 9999; 
		else fambrugaeldkvote = 0;
		if famrentudgpr      > 0 
			then famrentebyrde    = 9999; 
		else famrentebyrde    = 0; 
		if pension_skat     > 0 
			then fampenskvote     = 9999; 
		else fampenskvote     = 0;
	end;

	/*Pension assets and gross dept relative to income before tax, excl. pensions payments (udbetalinger) etc.*/
	if famindkekspens > 0 then do;
		fampenskvote2 = pension_skat / famindkekspens * 100;
		fambrugaeldkvote2 = famqpassivn / famindkekspens * 100;
	end;

	/*LTV: Liabilities in percent of total assets, excl. pension*/
	if famnyformue > 0 then do;
		fambelaaningsgrad = famqpassivn / famnyformue * 100;
	end;

	/*LTV for property assets: mortgage dept, bank dept, and mortgage deed dept in percent of total property assets*/
	if famboligvaerdi > 0 then do;
		fambelaan_bolig = (famoblgaeld + fambankgaeld + fampantgaeld) / famboligvaerdi_samlet * 100;
	end;
	
	label famindkefterskat = 'Family income after tax'
	      famnetgaeldkvote = 'Family net dept in percent of income after tax'
	      fambrugaeldkvote = 'Family gross dept in percent of income after tax'
	      famrentebyrde = 'Family�s interest expenses in percent of income after tax'
	      famandengaeldkvote = 'Family�s other dept in percent of income after tax'
	      famrealkgaeldkvote = 'Family�s mortgage dept in percent of income after tax'
	      famnetgaeldkvote_ubolig = 'Family net dept, excl. property, (assets excl. property - liabilities) in percent of income after tax'
	      famnetgaeldkvote_uandre = 'Family�s net dept, other assets aside (property value - liabilities) in percent of income after tax'
	      fambelaaningsgrad = 'LVT for the family�s assets';

	/* Assets and net dept variables INCL. pension assets*/
	famnyformue_net_pens = sum(famnyformue, pension_skat) - famqpassivn;
	if famindkefterskat > 0 then do;
	  famnetgaeldkvote_pens = -famnyformue_net_pens / famindkefterskat * 100;
	end;
	else if famindkefterskat le 0 then do;
		if famnyformue_net_pens lt 0 
			then famnetgaeldkvote_pens = 9999; 
		else famnetgaeldkvote_pens = 0;
	end;
	famnyformue_pens = sum(famnyformue, pension_skat);
	famnyformue_net_pens_ubolig = sum(famandreaktiver_pens, pension_skat) - famqpassivn;

	if famindkefterskat > 0 then do;
	  famnetgaeldkvote_pens_ubolig = -famnyformue_net_pens_ubolig / famindkefterskat * 100;
	end;
	else if famindkefterskat le 0 then do;
		if famnyformue_net_pens_ubolig lt 0 
			then famnetgaeldkvote_pens_ubolig = 9999; 
		else famnetgaeldkvote_pens_ubolig = 0;
	end;
	
	label famnyformue_pens      = 'Family�s total assets, market value of property, incl. pension assets'
	      famnyformue_net_pens  = 'Family�s net assets, market value of property, incl. pension assets'
	      famnetgaeldkvote_pens = 'Family�s net dept in percent of income after tax, incl. pensions assets'
		  famnetgaeldkvote_pens_ubolig = 'Family�s net dept in percent of after tax income, incl. pension assets, excl. real estate assets';

	/*Dummy for families with net dept*/
	if famnyformue_net < 0 
		then nettogaeld = 1; 
	else nettogaeld = 0;
	label nettogaeld = 'The family has positiv net dept';

	/*Dummy for families with net dept, incl. pension assets*/
	if famnyformue_net_pens < 0 
		then nettogaeld_pens = 1; 
	else nettogaeld_pens = 0;
	label nettogaeld_pens = 'The family has positiv net dept, incl. pension assets';

	/*Dummy for 'large gross dept' */
	if fambrugaeldkvote >= 400 and famqpassivn >= 50000 
		then hojgaeld = 1;
	else if famqpassivn ^= . 
		then hojgaeld = 0; 
	label hojgaeld = 'Gross dept ratio over 400 pct. and gross dept over 50.000'; 

	/*Dummy for families with retiree (old age pension, folkepension, and early retirement, efterl�n, - NOT incapacity benefit, f�rtidspension)*/
	length pensionist 3.;
	if socio13_1 in (322, 323) or socio13_2 in (322, 323) 
		then pensionist = 1; 
	else pensionist = 0;
	label pensionist = 'Retiree in family';

	/*Dummy for families with students*/
	length studerende 3.;
	if socio13_1 in (310) or socio13_2 in (310) 
		then studerende = 1; 
	else studerende = 0;
	label studerende = 'Student in family';

	/*Dummy for families with kontanthj�lpsmodtagere and recipients of incapacity benefits (f�rtidspensionister)*/
	length ikkeaktiv_andre 3.;
	if socio13_1 in (321,330) or socio13_2 in (321,330) 
		then ikkeaktiv_andre = 1; 
	else ikkeaktiv_andre = 0;
	label ikkeaktiv_andre = 'Cash benefits or recipient of incapacity benefit in family';

	/*Dummy for self employed and co-working spouse */
	if socio13_1 in (110, 111, 112, 113, 114, 120) or socio13_2 in (110, 111, 112, 113, 114, 120) /*I added 110 - MGP 1/8-18*/
		then selvst = 1; 
	else selvst = 0;
	label selvst = 'Self employed in family';

	/*Dummy for families with at leaste one adult member, who is NOT fully taxable*/
	if (familie_type in (1, 2, 3, 4) and famantalfskattepligtige in (0, 1)) 
			or (familie_type = 5 and famantalfskattepligtige = 0) 
		then ikkeskattepligtig = 1;
	else if (familie_type in (1, 2, 3, 4) and famantalfskattepligtige = 2) 
			or (familie_type = 5 and famantalfskattepligtige = 1) 
		then ikkeskattepligtig = 0; 
	label ikkeskattepligtig = 'Less than "fully taxable" to DK';

	/*Dummy for families, where at least one of the adults has experienced divorce or death of spouse within the last 2 year*/
	if afbrudtaegteskab_1 = 1 or afbrudtaegteskab_2 = 1 
		then famafbrudtaegteskab = 1;
	else if afbrudtaegteskab_1 = 0 
		then famafbrudtaegteskab = 0;
	label famafbrudtaegteskab = 'Marriage ended by divorce or death with the last 2 years (fam)';

	/*Dummy for families, where at least one of the adults has been unemployed in at least 6 of the paset 24 months*/
	if ledig24mnd_1 = 1 or ledig24mnd_2 = 1 
		then famledig24mnd = 1;
	else if ledig24mnd_1 = 0
		then famledig24mnd = 0;
	label famledig24mnd = 'At least 6 months unemployment within the past 24 months (fam)'; 

	/* Family's bond dept and dept to banks in percent of the total dept of the family*/
	if famqpassivn ^= 0 then do;
		famoblandel = famoblgaeld / famqpassivn * 100;
		fambankandel = fambankgaeld / famqpassivn * 100;
	end;
	label famoblandel  = 'Bond dept in percent of total dept (fam)'
	      fambankandel = 'Bank dept in percent of total dept (fam)';

	/* Age of the eldest adult in the family*/
	famalder = max(alder_1, alder_2);
	label famalder = 'Age of the eldest adult in the family';

	/* Education */
	uddniveau_1 = put(hfaudd_1, audd2011_l1l5_k.);
	uddniveau_2 = put(hfaudd_2, audd2011_l1l5_k.);

	if uddniveau_1 in (40, 50, 60, 65, 70) or uddniveau_2 in (40, 50, 60, 65, 70) 
		then famvidereudd = 1; 
	else famvidereudd = 0;
	if uddniveau_1 not in (20, 25, 30, 35, 39, 40, 50, 60, 65, 70) 
			and uddniveau_2 not in (20, 25, 30, 35, 39, 40, 50, 60, 65, 70) 
		then famufagl = 1; 
	else famufagl = 0;

	label uddniveau_1  = 'Main group for highest completed education, ident. person'
	      uddniveau_2  = 'Main group for highest completed education, partner'
	      famvidereudd = 'Higher education in family'
	      famufagl     = 'Only unskilled persons in the family'; 

	/* Hospitalization for adults in the family*/
	length famiants 3.;
	length fampdgs 3.;
	famiants = sum(iants_1, iants_2);
	fampdgs  = sum(pdgs_1, pdgs_2);
	label famiants = 'Number of hospitalizations (disease) for the adults of the family'
	      fampdgs = 'Days hospitalized (disease) for the adults of the family'
	;

	/* Saving and installment payments (afdrag) the past year*/

	*Savings variables;
	op_famindestpi = famindestpi - famindestpi_lag;
	op_fambankakt = (fambankakt - fambankakt_lag);
	op_famoblakt = (famoblakt - famoblakt_lag);
	op_fampantakt = (fampantakt - fampantakt_lag);
	op_famkursakt = (famkursakt - famkursakt_lag);
	op_famudlakt = (famudlakt - famudlakt_lag);

	*Installment payment variables;
	af_famoblgaeld   = (famoblgaeld_lag - famoblgaeld);
	af_famrestobl    = (famrestobl_lag - famrestobl);
	af_fambankgaeld  = (fambankgaeld_lag - fambankgaeld);
	af_fampantgaeld  = (fampantgaeld_lag - fampantgaeld);
	af_famudprigael  = (famudprigael_lag - famudprigael);
	af_famrealgaeld   = famafdrag_for; * From mortgage data - available from 2009;

	if aar > 2009 then af_famrealgaeld   = famafdrag_for; * Fra realkreditdata - kun tilg�ngelig fra 2009 og frem;
	if aar > 2009 & famsenest_optag = aar & famrestobl_lag ~=. then af_famrealgaeld = famrestobl_lag - famrestobl; * Optagelse af l�n;
	if aar > 2009 & famsenest_optag = aar & famrestobl_lag =. & famdispon_13_lag ~=. then af_famrealgaeld = - famrestobl; * Optagelse af l�n - famdispon_13-betingelse er for at sikre at familien findes foreg�ende �r;
	if aar > 2009 & famrestobl = . & famrestobl_lag ~=. then af_famrealgaeld = famrestobl_lag; * Indfrielse af l�n;

	/*Total effect of saving and installment payment on net assets, incl. pension assets, excl. real estate assets*/

	famopsp_netto = SUM(op_famindestpi, op_famkursakt, op_famudlakt, fampensindbetal, af_famoblgaeld, af_fambankgaeld, af_fampantgaeld, af_famudprigael);
	famopsp_netto_a = SUM(op_famindestpi, op_famkursakt, op_famudlakt, fampensindbetal, af_famrealgaeld, af_fambankgaeld, af_fampantgaeld, af_famudprigael);

	/* Computes a variables, which indicates the macro developement in stocks. It is calculated as the percentage change in the 
		stock index (from the KAX-index from Bloomberg 9/8-18) from the 4. quarter the year before to the 4. quarter this year. */
	IF aar = 1996 THEN makro_aktie = 31.10;
	ELSE IF aar = 1997 THEN makro_aktie = 39.00;
	ELSE IF aar = 1998 THEN makro_aktie = -6.94;
	ELSE IF aar = 1999 THEN makro_aktie = 21.31;
	ELSE IF aar = 2000 THEN makro_aktie = 22.45;
	ELSE IF aar = 2001 THEN makro_aktie = -19.81;
	ELSE IF aar = 2002 THEN makro_aktie = -20.30;
	ELSE IF aar = 2003 THEN makro_aktie = 37.46;
	ELSE IF aar = 2004 THEN makro_aktie = 15.77;
	ELSE IF aar = 2005 THEN makro_aktie = 38.10;
	ELSE IF aar = 2006 THEN makro_aktie = 17.74;
	ELSE IF aar = 2007 THEN makro_aktie = -6.56;
	ELSE IF aar = 2008 THEN makro_aktie = -41.67;
	ELSE IF aar = 2009 THEN makro_aktie = 33.56;
	ELSE IF aar = 2010 THEN makro_aktie = 27.42;
	ELSE IF aar = 2011 THEN makro_aktie = -15.87;
	ELSE IF aar = 2012 THEN makro_aktie = 26.27;
	ELSE IF aar = 2013 THEN makro_aktie = 25.65;
	ELSE IF aar = 2014 THEN makro_aktie = 17.64;
	ELSE IF aar = 2015 THEN makro_aktie = 17.88;
	ELSE IF aar = 2016 THEN makro_aktie = -1.20;
	ELSE IF aar = 2017 THEN makro_aktie = 15.37;
	
	LABEL makro_aktie = 'Macro developement in stock prices, in percent';

	/* Computes the family's imputed consumption*/

	/* Version 1: Without rent value of own property, with correction for developement in stock prices*/

	famforbrug1 = famdispindk															/* Disposable income, rent value of own property deducted */
				+ sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)			/* Single payments from ATP, LD og SP, withdrawn pensions*/         
				- sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)	/* Tax of the above single payments*/
				- op_famindestpi														/* Savings in bank deposits*/
				- (op_famkursakt - famkursakt_lag * makro_aktie / 100)					/* Savings in stocks*/
				- op_famudlakt															/* Savings in foreign assets*/
				- af_famoblgaeld														/* Installment payment (Afdrag) on mortgage*/
				- af_fambankgaeld														/* Payment (afdrag) of bank dept*/
				- af_fampantgaeld														/* Payment on mortgage deed dept (Afdrag p� pantebrevsg�ld) */
				- af_famudprigael														/* Payment (afdrag) on foreign dept*/
				- fampripensindbetal													/* Savings in private pension schemes*/
	;
	/* Version 1a: without rent value of own property, with correction for developement in stock prices, 
					without inclusion of foreign assets. 1a can be computed further back in time. */

	famforbrug1a = famdispindk															/* Disposable income, rent value of own property deducted */
				+ sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)			/* Single payments from ATP, LD og SP, withdrawn pensions*/         
				- sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)	/* Tax of the above single payments*/
				- op_famindestpi														/* Savings in bank deposits*/
				- (op_famkursakt - famkursakt_lag * makro_aktie / 100)					/* Savings in stocks*/
				- af_famoblgaeld														/* Installment payment (Afdrag) on mortgage */
				- af_fambankgaeld														/* Payment (afdrag) of bank dept */
				- af_fampantgaeld														/* Payment on mortgage deed dept (Afdrag p� pantebrevsg�ld) */
				- af_famudprigael														/* Payment (afdrag) on foreign dept*/
				- fampripensindbetal													/* Savings in private pension schemes*/
	;

	/* Alternative to version 1a. Savings in private pensions and payments on foreign dept are not accounted for, rest is the same*/

	famforbrug1a_alt = famdispindk															/* Disposable income, rent value of own property deducted */
				+ sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)			/* Single payments from ATP, LD og SP, withdrawn pensions*/         
				- sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)	/* Tax of the above single payments*/
				- op_famindestpi														/* Savings in bank deposits*/
				- (op_famkursakt - famkursakt_lag * makro_aktie / 100)					/* Savings in stocks*/
				- af_famoblgaeld														/* Installment payment (Afdrag) on mortgage */
				- af_fambankgaeld														/* Payment (afdrag) of bank dept */
				- af_fampantgaeld														/* Payment on mortgage deed dept (Afdrag p� pantebrevsg�ld) */
				;

/* Alternative to version 1a. Savings in private pensions are not accounted for and makro_aktie is excluded, rest is the same*/

	famforbrug1a_alt1 = famdispindk															/* Disposable income, rent value of own property deducted */
				+ sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)			/* Single payments from ATP, LD og SP, withdrawn pensions*/         
				- sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)	/* Tax of the above single payments*/
				- op_famindestpi														/* Savings in bank deposits*/
				- (op_famkursakt)														/* Savings in stocks, nor adjusted for price developement*/
				- af_famoblgaeld														/* Installment payment (Afdrag) on mortgage */
				- af_fambankgaeld														/* Payment (afdrag) of bank dept */
				- af_fampantgaeld														/* Payment on mortgage deed dept (Afdrag p� pantebrevsg�ld) */
				;

	/* Version 2: With rent value of own property, with correction for developement in stock prices, 
	computed payments (afdrag) from 2009 */
	/* 2a is based on the same method (change in market value of dept) in the entire period*/

	famforbrug2 = famdispon_13															/* Disposable income INCL. rent value of own property */
				+ sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)			/* Single payments from ATP, LD og SP, withdrawn pensions*/         
				- sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)	/* Tax of the above single payments*/
				- op_famindestpi														/* Savings in bank deposits*/
				- (op_famkursakt - famkursakt_lag * makro_aktie / 100)					/* Savings in stocks*/
				- op_famudlakt															/* Savings in foreign assets */
				- af_famoblgaeld														/* Installment payment (Afdrag) on mortgage*/
				- af_fambankgaeld														/* Payment (afdrag) of bank dept */
				- af_fampantgaeld														/* Payment on mortgage deed dept (Afdrag p� pantebrevsg�ld) */
				- af_famudprigael														/* Payment (afdrag) on foreign dept*/
				- fampripensindbetal													/* Savings in private pension schemes*/
	;

	famforbrug2a = famforbrug2; 
	af_famrealgaeld0 = af_famrealgaeld;
	if af_famrealgaeld = . then af_famrealgaeld0 = 0;

	if aar >= 2010 then 
	famforbrug2 = famdispon_13															/* Disposable income INCL. rent value of own property */
				+ sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)			/* Single payments from ATP, LD og SP, withdrawn pensions*/         
				- sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)	/* Tax of the above single payments*/
				- op_famindestpi														/* Savings in bank deposits*/
				- (op_famkursakt - famkursakt_lag * makro_aktie / 100)					/* Savings in stocks*/
				- op_famudlakt															/* Savings in foreign assets */
				- af_famrealgaeld0														/* Installment payment (Afdrag) on mortgage */
				- af_fambankgaeld														/* Payment (afdrag) of bank dept */
				- af_fampantgaeld														/* Payment on mortgage deed dept (Afdrag p� pantebrevsg�ld) */
				- af_famudprigael														/* Payment (afdrag) on foreign dept*/
				- fampripensindbetal													/* Savings in private pension schemes*/
	;


	/* Version 3: Without rent value of own property, with correction for developement in stock prices with 
					payment (afdrag) on mortgage */

	if aar >= 2010 then 
	famforbrug3 = famdispindk															/* Disposable income, rent value of own property deducted */
				+ sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)			/* Single payments from ATP, LD og SP, withdrawn pensions*/         
				- sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)	/* Tax of the above single payments*/
				- op_famindestpi														/* Savings in bank deposits*/
				- (op_famkursakt - famkursakt_lag * makro_aktie / 100)					/* Savings in stocks*/
				- op_famudlakt															/* Savings in foreign assets */
				- af_famrealgaeld0														/* Installment payment (Afdrag) on mortgage */
				- af_fambankgaeld														/* Payment (afdrag) of bank dept */
				- af_fampantgaeld														/* Payment on mortgage deed dept (Afdrag p� pantebrevsg�ld)*/
				- af_famudprigael														/* Payment (afdrag) on foreign dept*/
				- fampripensindbetal													/* Savings in private pension schemes*/
	;

drop af_famrealgaeld0;

	/* Consumption ratio: imputed consumption divided with disposable income, incl. single payments
Forbrugskvoter: Imputeret forbrug delt med disponibel indkomst, inkl. engangsudbetalinger*/

	if famdispindk > 0 then do;
		forbrugkvote1 = famforbrug1 / (famdispindk + sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)
		                               - sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)  );
		forbrugkvote1a_alt=famforbrug1a_alt/(famdispindk + sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)
		                               - sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)  );
		forbrugkvote2 = famforbrug2 / (famdispindk + sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)
		                               - sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)  );
		forbrugkvote3 = famforbrug3 / (famdispindk + sum(famatp_engang, famsp_pension, famld_udbetal, famhaevpen)
		                               - sum(famatp_engangaf, famsp_pensionaf, famld_udbetalaf, famhaevpenaf)  );
	end;

	if famperindkialt_13 > 0 then do;
		forbrugkvote1_perindkialt_13 = famforbrug1 / famperindkialt_13;
		forbrugkvote1aalt_perindkialt_13=famforbrug1a_alt/famperindkialt_13;
		forbrugkvote2_perindkialt_13 = famforbrug2 / famperindkialt_13;
		forbrugkvote3_perindkialt_13 = famforbrug3 / famperindkialt_13;
	end;

run &debug;

